package com.zwb.demo.easyweb.modules.database.service.impl;

import com.zwb.demo.easyweb.common.entity.response.CommonCode;
import com.zwb.demo.easyweb.common.entity.response.ResponseResult;
import com.zwb.demo.easyweb.core.exception.ExceptionCast;
import com.zwb.demo.easyweb.core.utils.ConvertUtils;
import com.zwb.demo.easyweb.core.utils.DbUtils;
import com.zwb.demo.easyweb.modules.database.entity.DatabaseColumnPO;
import com.zwb.demo.easyweb.modules.database.entity.DatabaseLinkPO;
import com.zwb.demo.easyweb.modules.database.entity.DatabaseTablePO;
import com.zwb.demo.easyweb.modules.database.repository.DatabaseColumnPORepository;
import com.zwb.demo.easyweb.modules.database.repository.DatabaseLinkPORepository;
import com.zwb.demo.easyweb.modules.database.repository.DatabaseTablePORepository;
import com.zwb.demo.easyweb.modules.database.service.DatabaseTableService;
import com.zwb.demo.easyweb.modules.database.vo.DatabaseColumnVO;
import com.zwb.demo.easyweb.modules.database.vo.DatabaseJdbcTableVO;
import com.zwb.demo.easyweb.modules.database.vo.DatabaseLinkVO;
import com.zwb.demo.easyweb.modules.database.vo.DatabaseTableVO;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import javax.sql.DataSource;
import java.util.List;
import java.util.Optional;

@Service
public class DatabaseTableServiceImpl implements DatabaseTableService {
    private final DatabaseTablePORepository databaseTablePORepository;
    private final DatabaseLinkPORepository databaseLinkPORepository;
    private final DatabaseColumnPORepository databaseColumnPORepository;
    private final DatabaseColumnServiceImpl databaseColumnService;
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public DatabaseTableServiceImpl(
            DatabaseTablePORepository databaseTablePORepository,
            DatabaseLinkPORepository databaseLinkPORepository,
            DatabaseColumnPORepository databaseColumnPORepository,
            DatabaseColumnServiceImpl databaseColumnService, JdbcTemplate jdbcTemplate) {
        this.databaseTablePORepository = databaseTablePORepository;
        this.databaseLinkPORepository = databaseLinkPORepository;
        this.databaseColumnPORepository = databaseColumnPORepository;
        this.databaseColumnService = databaseColumnService;
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public ResponseResult findAllByDatabaseLinkId(String uuid) {
        if (StringUtils.isBlank(uuid)) {
            ExceptionCast.cast(CommonCode.ILLEGAL_PARAM);
        }
        List<DatabaseTablePO> allPo = databaseTablePORepository.findAllByDatabaseId(uuid);
        List<DatabaseTableVO> allVo = ConvertUtils.convertList(allPo, DatabaseTableVO.class);
        for (DatabaseTableVO vo : allVo) {
            Optional<DatabaseLinkPO> optional =
                    databaseLinkPORepository.findById(vo.getDatabaseId());
            optional.ifPresent(
                    databaseLinkPO -> vo.setDatabaseName(databaseLinkPO.getDatabaseName()));
        }
        return ResponseResult.ok().body(allVo);
    }

    @Override
    public ResponseResult findJdbcTableByDatabaseLinkId(String uuid) {
        if (StringUtils.isBlank(uuid)) {
            ExceptionCast.cast(CommonCode.ILLEGAL_PARAM);
        }
        Optional<DatabaseLinkPO> optional = databaseLinkPORepository.findById(uuid);
        if (optional.isPresent()) {
            String sql =
                    "select table_name,table_comment  from information_schema.tables where table_schema=?;";
            DatabaseLinkPO po = optional.get();
            DatabaseLinkVO vo = ConvertUtils.convertObject(po, DatabaseLinkVO.class);
            DataSource dataSource = DbUtils.getDataSource(vo);
            jdbcTemplate.setDataSource(dataSource);
            List<DatabaseJdbcTableVO> databaseJdbcTableVOs =
                    jdbcTemplate.query(
                            sql,
                            new BeanPropertyRowMapper<>(DatabaseJdbcTableVO.class),
                            vo.getDatabaseName());
            return ResponseResult.ok().body(databaseJdbcTableVOs);
        }
        ExceptionCast.cast(CommonCode.ID_NO_EXITS);
        return null;
    }

    @Override
    public ResponseResult importFromDatabase(String databaseId, List<String> tables) {
        if (StringUtils.isBlank(databaseId)) {
            ExceptionCast.cast(CommonCode.ILLEGAL_PARAM);
        }
        if (CollectionUtils.isEmpty(tables)) {
            ExceptionCast.cast(CommonCode.ILLEGAL_PARAM);
        }
        Optional<DatabaseLinkPO> optional = databaseLinkPORepository.findById(databaseId);
        if (!optional.isPresent()) {
            ExceptionCast.cast(CommonCode.ID_NO_EXITS);
        }
        DatabaseLinkPO linkPO = optional.get();
        DatabaseLinkVO linkVO = ConvertUtils.convertObject(linkPO, DatabaseLinkVO.class);
        DataSource dataSource = DbUtils.getDataSource(linkVO);
        jdbcTemplate.setDataSource(dataSource);
        String SQL =
                "SELECT table_name as tableName, table_comment as tableDesc FROM information_schema. TABLES WHERE table_schema = ? and table_name=?;";
        String SQL2 =
                "SELECT COLUMN_NAME as columnName, IS_NULLABLE as nullable, COLUMN_TYPE as columnType, COLUMN_COMMENT as columnDesc, ORDINAL_POSITION as columnOrder, IF(COLUMN_KEY = 'PRI','1', '0') AS columnKey FROM information_schema. COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?;";
        tables.forEach(
                s -> {
                    List<DatabaseTablePO> list =
                            jdbcTemplate.query(
                                    SQL,
                                    new BeanPropertyRowMapper<>(DatabaseTablePO.class),
                                    linkVO.getDatabaseName(),
                                    s);
                    if (!CollectionUtils.isEmpty(list)) {
                        DatabaseTablePO po = list.get(0);
                        po.setDatabaseId(linkVO.getId());
                        po.setTableType(linkVO.getDatabaseType());
                        po.setGenerationStrategy(1);
                        DatabaseTablePO save = databaseTablePORepository.save(po);
                        List<DatabaseColumnPO> columnVOS =
                                jdbcTemplate.query(
                                        SQL2,
                                        new BeanPropertyRowMapper<>(DatabaseColumnPO.class),
                                        linkVO.getDatabaseName(),
                                        s);
                        for (DatabaseColumnPO columnVO : columnVOS) {
                            columnVO.setTableId(save.getId());
                            databaseColumnPORepository.save(columnVO);
                        }
                    }
                });

        return ResponseResult.ok();
    }

    @Override
    @Transactional
    public ResponseResult deleteOne(String uuid) {
        if (StringUtils.isBlank(uuid)) {
            ExceptionCast.cast(CommonCode.ILLEGAL_PARAM);
        }
        Optional<DatabaseTablePO> optional = databaseTablePORepository.findById(uuid);
        if (!optional.isPresent()){
            ExceptionCast.cast(CommonCode.ID_NO_EXITS);
        }
        databaseTablePORepository.deleteById(uuid);
        databaseColumnPORepository.deleteAllByTableId(uuid);
        return ResponseResult.ok();
    }

    @Override
    @Transactional
    public ResponseResult saveOne(DatabaseTableVO vo) {
        if (!StringUtils.isBlank(vo.getId())){
            Optional<DatabaseTablePO> optional = databaseTablePORepository.findById(vo.getId());
            if (!optional.isPresent()){
                ExceptionCast.cast(CommonCode.ID_NO_EXITS);
            }
            DatabaseTablePO po = optional.get();
            BeanUtils.copyProperties(vo, po);
            databaseTablePORepository.save(po);
            for (DatabaseColumnVO columnVO : vo.getColumns()) {
                databaseColumnService.addOrUpdateOne(columnVO);
            }
        }else {
            DatabaseTablePO po = ConvertUtils.convertObject(vo, DatabaseTablePO.class);
            databaseTablePORepository.save(po);
            for (DatabaseColumnVO columnVO : vo.getColumns()) {
                databaseColumnService.addOrUpdateOne(columnVO);
            }
        }
        return ResponseResult.ok();
    }
}
